package db;

import loadbean.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Create by chenhd on 2021/9/4.
 */
@Component
public class JdbcOperator {

    @Autowired
    JdbcTemplate jdbcTemplate;

    Connection connection;

    public JdbcOperator() throws Exception {
        this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root123");
    }

    /**
     * jdbcTemplate hikari
     *
     * @return
     */
    public List<Student> queryStudents() {
        List<Student> studentList = new ArrayList<>();
        String sql = "select * from t_student";
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                Student student = new Student();
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                studentList.add(student);
            }
        });
        return studentList;
    }

    /**
     * 原生JDBC
     *
     * @return
     */
    public boolean delStudentsByJdbc() {
        String sql = "delete from t_student where name='zhangsan'";
        try (Statement statement = connection.createStatement()) {
            return statement.execute(sql);
        } catch (Exception e) {
            return false;
        }
    }


    /**
     * prepareStatement
     *
     * @return
     */
    public boolean delStudentsByPrepareStatement() {
        String sql = "delete from t_student where name=?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setString(1, "zhangsan");
            return preparedStatement.execute();
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * prepareStatement批处理+事务
     *
     * @throws SQLException
     */
    public void addStudentByBatch() throws SQLException {
        String sql = "insert into t_student(name,age) values(?,?)";
        connection.setAutoCommit(false);
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 0; i < 10; i++) {
                preparedStatement.setString(1, "zhangsan" + i);
                preparedStatement.setInt(2, 20);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            connection.commit();
        } catch (Exception e) {
            connection.rollback();
        }
    }


}
